﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace Library
{
    public class clCongNo
    {
        private SqlConnection sqlcon;
        private SqlCommand sqlcmd;
        public static SqlConnection GetConnection()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["StrConnectString"].ToString();
            SqlConnection sqlcon = new SqlConnection(strConnect);
            return sqlcon;
        }

        public int themKhachHangNo(string tbsudung, Int64 idkh, Int16 trangThaiNo)
        {
            int themcongno = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_CongNo_QuanLy", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@tbsudung", SqlDbType.NVarChar, 30).Value = tbsudung;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@trangThaiNo", SqlDbType.SmallInt).Value = trangThaiNo;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    themcongno = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return themcongno;
        }

        public DataTable danhSachCongNo()
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_CongNo_DanhSach", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int trano(Int64 idkh, Int64 sotientra, Int64 thangnamno, string ngaytra)
        {
            int tracongno = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_CongNo_TraTien", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@sotientra", SqlDbType.BigInt).Value = sotientra;
                    sqlcmd.Parameters.Add("@ThangNamNo", SqlDbType.BigInt).Value = thangnamno;
                    sqlcmd.Parameters.Add("@ngaytra", SqlDbType.NVarChar,50).Value = ngaytra;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    tracongno = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return tracongno;
        }

        public DataTable chitietcongno(Int64 idkh, Int64 thangnamno)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_CongNo_chitiet", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@thangNamNo", SqlDbType.BigInt).Value = thangnamno;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public int xoacongno(Int64 idkh, Int64 thangnam)
        {
            int xoano = -1;
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_CongNo_Xoa", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@idkh", SqlDbType.BigInt).Value = idkh;
                    sqlcmd.Parameters.Add("@ThangNamNo", SqlDbType.BigInt).Value = thangnam;
                    sqlcmd.ExecuteNonQuery();
                    sqlcon.Close();
                    xoano = 1;
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return xoano;
        }
    }
}
